package com.sss.common.easyexcel.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.property.ExcelWriteHeadProperty;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author: sss
 * @date: 2019-09-06 13:53
 **/
public class EasyExcelUtil {



    /**
     *  读取excel文件内容
     * @param is 输入流
     * @param clz 模型类
     * @return java.util.List<T>
     * @author WYY-SSS
    **/
    public static <T> List<T> readExcel(InputStream is, Class<T> clz) {

        SyncReadListener tmpListener = new SyncReadListener();
        ReadSheet readSheet = new ReadSheet();
        readSheet.setClazz(clz);
        EasyExcel.read(is).registerReadListener(tmpListener).build().read(readSheet);
        return (List<T>) tmpListener.getList();
    }
    /**
     *  根据文件路径读取excel文件内容
     * @param filePath excel文件路径
     * @param clz 模型类
     * @return java.util.List<T>
     * @author WYY-SSS
    **/
    public static <T> List<T> readExcelByFilePath(String filePath, Class<T> clz) {
        if (StringUtils.isEmpty(filePath)) {
            Assert.notNull(filePath,"文件路径不能为空");
        }
        String s = filePath.substring(filePath.lastIndexOf("\\") + 1);
        boolean b = s.endsWith(".xlsx") || s.endsWith(".xls");
        Assert.isTrue(b,"不是excel文件,读取错误!");
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        SyncReadListener tmpListener = new SyncReadListener();
        ReadSheet readSheet = new ReadSheet();
        readSheet.setClazz(clz);
        EasyExcel.read(inputStream).registerReadListener(tmpListener).build().read(readSheet);
        return (List<T>) tmpListener.getList();
    }

    /**
     *  读取excel文件中某一个sheet的内容
     * @param is 输入流
     * @param clz 模型类
     * @return java.util.List<T>
     * @author WYY-SSS
     **/
    public static <T> List<T> readExcelBySheetNumber(InputStream is, Class<T> clz,Integer sheetNo) {
        SyncReadListener tmpListener = new SyncReadListener();
        ReadSheet readSheet = new ReadSheet(sheetNo);
        readSheet.setClazz(clz);
        EasyExcel.read(is).registerReadListener(tmpListener).build().read(readSheet);
        return (List<T>) tmpListener.getList();
    }

    /**
     * 自定义表头的excel文件导出
     * @param os
     * @param data  写入excel文件的数据类型
     * @param clz 模型类
     * @param simpleHead 自定义表头
     * @return void
     * @author WYY-SSS
    **/
    public static <T> void writeListTo(OutputStream os, List<T> data, Class<T> clz, List<String> simpleHead) {
        WriteSheet writeSheet = new WriteSheet();
        writeSheet.setClazz(clz);
        writeSheet.setNeedHead(true);

        List<List<String>> head = simpleHead.stream().map(a -> Arrays.asList(a)).collect(Collectors.toList());
        writeSheet.setHead(head);
        ExcelWriter write = EasyExcel.write(os).build();
        write.write(data, writeSheet);
        write.finish();
    }

    /**
     * 单个sheet的excel文件导出
     * @param data 写入excel文件的数据类型
     * @param clz excel模型类
     * @param sheetName  sheet名称
     * @return void
     * @author SSS
     **/
    public static <T> void writeExcel(String fileName,List<T> data, Class<T> clz,String sheetName) {

        // 自定义内容样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = setHorizontalCellStyleStrategy();
        //列宽度自适应
        LongestMatchColumnWidthStyleStrategy columnWidthStyleStrategy = new LongestMatchColumnWidthStyleStrategy();
        ExcelWriter write = EasyExcel.write(getOutputStream(fileName)).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(columnWidthStyleStrategy).build();
        WriteSheet writeSheet = new WriteSheet();
        writeSheet.setSheetName(sheetName);
        writeSheet.setClazz(clz);
        write.write(data, writeSheet);
        write.finish();
    }
    /**
     * 导出excel文件到指定文件路径
     * @param data 写入excel文件的数据类型
     * @param clz excel模型类
     * @param sheetName  sheet名称
     * @return void
     * @author SSS
     **/
    public static <T> void writeExcelByFilePath(String filePath,List<T> data, Class<T> clz,String sheetName) {
        if (StringUtils.isEmpty(filePath)) {
            Assert.notNull(filePath,"文件路径不能为空");
        }
        String s = filePath.substring(filePath.lastIndexOf("\\") + 1);
        boolean b = s.endsWith(".xlsx") || s.endsWith(".xls");
        Assert.isTrue(b,"不是excel文件,写入错误!");
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(filePath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        // 自定义内容样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = setHorizontalCellStyleStrategy();
        //列宽度自适应
        LongestMatchColumnWidthStyleStrategy columnWidthStyleStrategy = new LongestMatchColumnWidthStyleStrategy();
        ExcelWriter write = EasyExcel.write(outputStream ).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(columnWidthStyleStrategy).build();
        WriteSheet writeSheet = new WriteSheet();
        writeSheet.setSheetName(sheetName);
        writeSheet.setClazz(clz);
        write.write(data, writeSheet);
        write.finish();
    }


    /**
     * 多个sheet的excel文件导出
     * @param data 写入excel文件的数据类型
     * @param clz excel模型类
     * @param sheetNo 第几个sheet
     * @param sheetName  sheet名称
     * @return void
     * @author SSS
    **/
    public static <T> void writeManySheetExcel(String fileName, List<T> data, Class<T> clz,Integer sheetNo,String sheetName) {

        // 自定义内容样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = setHorizontalCellStyleStrategy();
        //列宽度自适应
        LongestMatchColumnWidthStyleStrategy columnWidthStyleStrategy = new LongestMatchColumnWidthStyleStrategy();
        ExcelWriter write = EasyExcel.write(getOutputStream(fileName)).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(columnWidthStyleStrategy).build();
        WriteSheet writeSheet = new WriteSheet();
        writeSheet.setSheetName(sheetName);
        writeSheet.setSheetNo(sheetNo);
        writeSheet.setClazz(clz);
        write.write(data, writeSheet);
        write.finish();
    }

    /**
     * 自定义列标题和内容的样式
     * @return com.alibaba.excel.write.style.HorizontalCellStyleStrategy
     * @author WSS
    **/
    public static  HorizontalCellStyleStrategy setHorizontalCellStyleStrategy(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色设置
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)15);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        //  contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        //  contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)12);
        //是否加粗
        contentWriteFont.setBold(true);
        contentWriteFont.setFontName("微软雅黑");
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        //设置 自动换行
       // contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//        //设置边框样式
//       contentWriteCellStyle.setBorderLeft(BorderStyle.MEDIUM);
        //颜色
//      contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
//      contentWriteCellStyle.setBorderTop(DASHED);
//      contentWriteCellStyle.setBorderRight(DASHED);
//      contentWriteCellStyle.setBorderBottom(DASHED);

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }


    /**
     * 根据输入流，判断为xls还是xlsx，该方法原本存在于easyexcel 1.1.0 的ExcelTypeEnum中。
     */
    public static ExcelTypeEnum valueOf(InputStream inputStream) {
        try {
            FileMagic fileMagic = FileMagic.valueOf(inputStream);
            if (FileMagic.OLE2.equals(fileMagic)) {
                return ExcelTypeEnum.XLS;
            }
            if (FileMagic.OOXML.equals(fileMagic)) {
                return ExcelTypeEnum.XLSX;
            }
            throw new IllegalArgumentException("excelTypeEnum can not null");

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据文件路径获取Workbook对象
     * @param filepath 文件全路径
     */
    public static Workbook getWorkbook(String filepath) {
        File fi = new File(filepath);
        //读取excel模板
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(fi);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return xssfWorkbook;
    }


    /**
     * 导出文件时为Writer生成OutputStream.
     *
     * @param fileName 文件名
     * @return SSS
     */
    private static OutputStream getOutputStream(String fileName)  {
        HttpServletResponse response = getResponse();
        HttpServletRequest request = getRequest();
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
           // response.setContentType("application/x-download");
            response.setCharacterEncoding("utf8");
            if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
                // firefox浏览器
                response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + fileName+".xlsx");
            } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
                // IE浏览器
                fileName = URLEncoder.encode(fileName, "UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName+".xlsx");
            }else if (request.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) {
                // 谷歌
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName+".xlsx");
            }else {
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName+".xlsx");
            }
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    private static ServletRequestAttributes getRequestAttributes() {
        return ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes());
    }

    private static HttpServletRequest getRequest() {
        return getRequestAttributes().getRequest();
    }

    private static HttpServletResponse getResponse() {
        return getRequestAttributes().getResponse();
    }
 }
